﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace BestBuy.DAL
{
    public class CThongTinCaNhan
    {
        private SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["TradeKeyConnectionString"].ConnectionString);
        private TradeKeyDataContext content = new TradeKeyDataContext();
        public CThongTinCaNhan() { }

        public string MaHoaMatKhau(string matkhau)
        {
            string str = "";
            Byte[] mangpam = System.Text.Encoding.UTF8.GetBytes(matkhau);
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            mangpam = md5.ComputeHash(mangpam);
            foreach (Byte b in mangpam)
            {
                str += b.ToString("x2");
            }
            return str;
        }

        public ThongTinTaiKhoan CheckLogin(string email, string matkhau) {
            string mahoa = this.MaHoaMatKhau(matkhau);
            var q = (from tttk in content.ThongTinTaiKhoans
                     where tttk.Email == email && tttk.MatKhau == mahoa && tttk.TinhTrang == true
                     select tttk).FirstOrDefault();
            return q;
        }

        public ThongTinTaiKhoan GetAccountID(int idtaikhoan) {
            var q = (from sp in content.ThongTinTaiKhoans
                     where sp.IDTaiKhoan == idtaikhoan
                     select sp).FirstOrDefault();
            return q;
        }

        public bool CheckPassword(int idtaikhoan, string matkhau) {
            string mk = this.GetAccountID(idtaikhoan).MatKhau.ToString();
            string mahoamk = this.MaHoaMatKhau(matkhau);
            if (mk.Equals(mahoamk)) {
                return true;
            }
            return false;
        }

        public bool UpdatePassword(int idtaikhoan,string matkhau) {
            string mahoamk = this.MaHoaMatKhau(matkhau);
            ThongTinTaiKhoan tk = this.GetAccountID(idtaikhoan);
            tk.MatKhau = mahoamk;
            try
            {
                content.SubmitChanges();
            }
            catch { 
                return false;
            }
            return true;
        }

        public bool LockAccount(int id) {
            ThongTinTaiKhoan tk = this.GetAccountID(id);
            tk.TinhTrang = false;
            try
            {
                content.SubmitChanges();
            }
            catch {
                return false;
            }
            return true;
        }

        public bool EmailExistTest(string email) {
            var q = (from sp in content.ThongTinTaiKhoans
                     where sp.Email.ToUpper() == email.ToUpper()
                     select sp).FirstOrDefault();
            if (q == null)
                return true;
            return false;
        }

        public bool InsertInfoUser(ThongTinTaiKhoan tt)
        {
            try
            {
                content.ThongTinTaiKhoans.InsertOnSubmit(tt);
                content.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public int soLuongDuLieu(string strQuery)
        {
            sqlCon.Open();
            string str = "select COUNT(*) from ThongTinTaiKhoan dl where dl.TinhTrang='True'" + strQuery;
            SqlCommand cmd = new SqlCommand(str, sqlCon);
            int soLuong = Convert.ToInt32(cmd.ExecuteScalar());
            sqlCon.Close();
            return soLuong;
        }
        public int slTrangDuLieu(int soLuongTrenTrang, string strQuery)
        {
            int soLuong = this.soLuongDuLieu(strQuery);
            if (soLuong > 0)
            {
                int soTrang = soLuong / soLuongTrenTrang;
                if (soLuong % soLuongTrenTrang != 0)
                {
                    soTrang++;
                }

                return soTrang;
            }
            return -1;
        }

        public int[] FilterMaDuLieu(string strWhere, int startPoint, int size)
        {
            DataTable dt = new DataTable();
            int[] arrMaDuLieu = new int[size];

            string sql = "select dl.IDTaiKhoan,ROW_NUMBER() over (order by dl.IDTaiKhoan desc) as row_num from ThongTinTaiKhoan dl where  dl.TinhTrang='True'" + strWhere;
            string sql1 = "select distinct(t0.IDTaiKhoan) from (" + sql + ") as t0 where t0.row_num between " + (startPoint + 1) + " and " + (startPoint + size);
            sqlCon.Open();
            SqlCommand cmd = new SqlCommand(sql1, sqlCon);
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                arrMaDuLieu[i] = int.Parse(dr["IDTaiKhoan"].ToString());
                i++;
            }

            sqlCon.Close();

            return arrMaDuLieu;
        }

        public List<ThongTinTaiKhoan> GetList(string strQuery,int begin,int size)
        {
            int[] arrMaDuLieu = this.FilterMaDuLieu(strQuery, begin, size);

            var result = (from dl in content.ThongTinTaiKhoans
                          where arrMaDuLieu.Contains(dl.IDTaiKhoan)
                          orderby dl.IDTaiKhoan descending
                          select dl).ToList();
            return result;
        }

        public ThongTinTaiKhoan GetAcountOfCompany(int idcongty) {
            var q = (from dl in content.ThongTinTaiKhoans
                     where dl.IDCongTy == idcongty
                     select dl).FirstOrDefault();
            return q;
        }

        public bool UpdateUser(ThongTinTaiKhoan tk)
        {
            var q = (from sp in content.ThongTinTaiKhoans
                     where sp.IDTaiKhoan == tk.IDTaiKhoan
                     select sp).FirstOrDefault();
            q.Email = tk.Email;
            q.GioiTinh = tk.GioiTinh;
            q.HoTen = tk.HoTen;
            q.IDCongTy = tk.IDCongTy;
            q.IDCongViec = tk.IDCongViec;
            q.LoaiND = tk.LoaiND;
            q.LoiMoi = tk.LoiMoi;
            q.QuocGia = tk.QuocGia;
            q.SanPhamBan = tk.SanPhamBan;
            q.SanPhamMua = tk.SanPhamMua;
            q.Duong = tk.Duong;
            q.ThanhPho = tk.ThanhPho;
            q.Tinh = tk.Tinh;
            q.Skype = tk.Skype;
            q.Yahoo = tk.Yahoo;
            try
            {
                content.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}